/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dal;

import entity.Event;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hailk_c00639
 */
public class EventDAL {

    MyConnection con = new MyConnection();

    public List<Event> getAllEvent() throws SQLException {
        List<Event> lst = new ArrayList<>();

        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_GetAllEvent}");
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                Event event = new Event();
                event.setEventID(rs.getInt("EventID"));
                event.setEventName(rs.getString("EventName"));
                event.setFieldName(rs.getString("FieldName"));
                event.setCreatedDate(rs.getDate("CreatedDate"));
                event.setStartDate(rs.getDate("StartDate"));
                event.setFinishDate(rs.getDate("FinishDate"));
                event.setAddress(rs.getString("Address"));
                event.setSpeakerName(rs.getString("SpeakerName"));
                event.setImageUrl(rs.getString("ImageUrl"));
                event.setDescription(rs.getString("Description"));
                event.setFee(rs.getFloat("Fee"));
                event.setEventStatus(rs.getString("EventStatus"));

                lst.add(event);
            }

        } catch (SQLException ex) {
            Logger.getLogger(EventDAL.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }
        finally
        {
            con.closeDB();
        }
        return lst;
    }

    public Event getEventByName(String eventName) throws SQLException {
        Event event = null;

        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_GetEventByName(?)}");
            call.setString(1, eventName);
            ResultSet rs = call.executeQuery();
            if (rs.next()) {
                event.setEventID(rs.getInt("EventID"));
                event.setFieldID(rs.getInt("FieldID"));
                event.setCreatedDate(rs.getDate("CreatedDate"));
                event.setStartDate(rs.getDate("StartDate"));
                event.setFinishDate(rs.getDate("FinishDate"));
                event.setAddress(rs.getString("Address"));
                event.setSpeakerName(rs.getString("SpeakerName"));
                event.setImageUrl(rs.getString("ImageUrl"));
                event.setDescription(rs.getString("Description"));
                event.setFee(rs.getFloat("Fee"));
                event.setEventStatus(rs.getString("EventStatus"));
            } else {
                // Neu sai thi thong bao loi
            }

        } catch (SQLException ex) {
            Logger.getLogger(EventDAL.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }
        finally
        {
            con.closeDB();
        }
        return event;
    }

    public boolean insertEvent(Event event) throws SQLException {
        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_InsertEvent(?,?,?,?,?,?,?,?,?,?,?)}");
            call.setString(1, event.getEventName());
            call.setInt(2, event.getFieldID());
            call.setDate(3, event.getCreatedDate());
            call.setDate(4, event.getStartDate());
            call.setDate(5, event.getFinishDate());
            call.setString(6, event.getAddress());
            call.setString(7, event.getSpeakerName());
            call.setString(8, event.getImageUrl());
            call.setString(9, event.getDescription());
            call.setFloat(10, event.getFee());
            call.setString(11, event.getEventStatus());

            call.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(EventDAL.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }
        finally
        {
            con.closeDB();
        }
        return true;
    }

    public boolean updateEvent(Event event) throws SQLException {
        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_UpdateEvent(?,?,?,?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, event.getEventID());
            call.setString(2, event.getEventName());
            call.setInt(3, event.getFieldID());
            call.setDate(4, event.getCreatedDate());
            call.setDate(5, event.getStartDate());
            call.setDate(6, event.getFinishDate());
            call.setString(7, event.getAddress());
            call.setString(8, event.getSpeakerName());
            call.setString(9, event.getImageUrl());
            call.setString(10, event.getDescription());
            call.setFloat(11, event.getFee());
            call.setString(12, event.getEventStatus());

            call.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(EventDAL.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }
        finally
        {
            con.closeDB();
        }
        return true;
    }

    public boolean deleteEvent(int eventID) throws SQLException {
        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_DeleteEvent(?)}");
            call.setInt(1, eventID);
            call.execute();

        } catch (SQLException ex) {
            Logger.getLogger(EventDAL.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }
        finally
        {
            con.closeDB();
        }
        return true;
    }

    public List<Event> searchEventByName(String eventName) throws SQLException {
        List<Event> lst = new ArrayList<>();

        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_SearchEventByName(?)}");
            call.setString(1, eventName);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                Event event = new Event();
                event.setEventID(rs.getInt("EventID"));
                event.setEventName(rs.getString("EventName"));
                event.setFieldID(rs.getInt("FieldID"));
                event.setCreatedDate(rs.getDate("CreatedDate"));
                event.setStartDate(rs.getDate("StartDate"));
                event.setFinishDate(rs.getDate("FinishDate"));
                event.setAddress(rs.getString("Address"));
                event.setSpeakerName(rs.getString("SpeakerName"));
                event.setImageUrl(rs.getString("ImageUrl"));
                event.setDescription(rs.getString("Description"));
                event.setFee(rs.getFloat("Fee"));
                event.setEventStatus(rs.getString("EventStatus"));

                lst.add(event);
            }

        } catch (SQLException ex) {
            Logger.getLogger(EventDAL.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        }
        finally
        {
            con.closeDB();
        }
        return lst;
    }
}
